ALTER     procedure sp_check_lot_status(
@lot_number varchar(50), 
@customer_po varchar(50),
@customer_so varchar(50),
@invoice_number varchar(50),
@part_number varchar(50), 
@lead_count int,
@package_type varchar(50),
@current_location varchar(50),
@next_location varchar(50),
@ship_date datetime,
@ship_qty int,
@mawb varchar(50),
@hawb varchar(50),
@flight_number varchar(50),
@etd datetime,
@eta datetime,
@vendor_name varchar(50),
@ps_type varchar(50),
@lot_id bigint out
)
as
begin

		
		declare @process_step_id int
		declare @location_id int	
		declare @normal_cost float
		
		if @current_location = 'Class Store' and @vendor_name = 'GTC'
		begin
		   
		   select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @part_number and  PS_Type_Name like 'Class Store'  and product.ProcessStep_ID=Process_Step.ProcessStep_ID 
		  
		   if @process_step_id is null
		   begin
			-- dummy update to avoid SQLException
			update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
			return
  		   end
	 	   select @location_id = Location_ID from Location where L_Type = 'Class Store' and L_Vendor_Name = @vendor_name
		   select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
		   
		   if  @lot_id is null
	 	   begin
			
	   		select @process_step_id =ProcessStep_ID from fn_get_process_flow_by_processstep_id(@process_step_id)  where ps_type='FT1'
			select @location_id = Location_ID from Location where L_Type = 'Test' and L_Vendor_Name = @vendor_name
			if @process_step_id is null -- new Product or Product Name mismatch
			begin
				select @process_step_id = Process_Step.ProcessStep_ID from Product_Map_Table,Process_Step where Product_Map_Table.part_number = @part_number and PS_Type_Name='Test' and PS_Type='FT1' and Product_Map_Table.ProcessStep_ID=Process_Step.ProcessStep_ID and Product_Map_Table.Location_ID = @location_id
				if @process_step_id is null
				begin
					exec sp_create_update_miscellaneous_lot @part_number, @lot_number, null, null, null,
					@vendor_name,'FT1', 'TEST', null, @ship_date, null, null, null, null, null, @ship_qty, null, null, null,'TRANSIT'

					-- dummy update to avoid SQLException
					update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
					return
				end
			end

			select  @lot_id = lot_id from lot_table where lot_number = @lot_number  and ProcessStep_ID = @process_step_id
			  -- print 'the lot_id' + convert(varchar(50),@lot_id)
			if @lot_id is null
			begin
			    select @normal_cost = PSLT_Cost_Normal from Process_Step_Location_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id
		 	    if @ship_qty is not null and @ship_qty <> 0
		  	    begin
				insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Lot_Cost, Lot_Current_Status, Timestamp) values (@process_step_id, @location_id, @lot_number, (@normal_cost * @ship_qty), 'Close', CURRENT_TIMESTAMP)
			    end
			    select @lot_id = @@IDENTITY
			   -- print 'the lotid' + convert(varchar(50),@lot_id)
		        end
		  end	
	  	end
		if @current_location = 'Test' and @vendor_name = 'GTC'
		begin


		   select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @part_number and  PS_Type_Name like 'Test'  and product.ProcessStep_ID=Process_Step.ProcessStep_ID 
		   select @location_id = Location_ID from Location where L_Type = 'Test' and L_Vendor_Name = @vendor_name		
		   if @process_step_id is null
		   begin
			select @process_step_id = Process_Step.ProcessStep_ID from Product_Map_Table,Process_Step where Product_Map_Table.part_number = @part_number and PS_Type_Name='Test' and PS_Type=@ps_type and Product_Map_Table.ProcessStep_ID=Process_Step.ProcessStep_ID and Product_Map_Table.Location_ID = @location_id
			if @process_step_id is null
			begin
				exec sp_create_update_miscellaneous_lot @part_number, @lot_number, null, null, null,
				@vendor_name, @ps_type, 'TEST', null, @ship_date, null, null, null, null, null, @ship_qty, null, null, null,'TRANSIT'

				-- dummy update to avoid SQLException
				update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
				return
			end		
			
			   
  		   end
	 	   
		   select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number

		end	
		if @current_location = 'Finished Goods' and @vendor_name = 'GTC'
		begin

		   select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @part_number and  PS_Type_Name like 'Finished Goods'  and product.ProcessStep_ID=Process_Step.ProcessStep_ID 
	 	   select @location_id = Location_ID from Location where L_Type = 'Finished Goods' and L_Vendor_Name = @vendor_name
		   if @process_step_id is null
		   begin
				select @process_step_id = Process_Step.ProcessStep_ID from Product_Map_Table,Process_Step where Product_Map_Table.part_number = @part_number and PS_Type_Name='Finished Goods' and PS_Type=@ps_type and Product_Map_Table.ProcessStep_ID=Process_Step.ProcessStep_ID and Product_Map_Table.Location_ID = @location_id
				if @process_step_id is null
				begin
					exec sp_create_update_miscellaneous_lot @part_number, @lot_number, null, null, null,
					@vendor_name,@ps_type, 'Finished Goods', null, @ship_date, null, null, null, null, null, @ship_qty, null, null, null,'TRANSIT'

					-- dummy update to avoid SQLException
					update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
					return
				end
			
  		   end

		   select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number

		 
				
		  
		  if @lot_id is null
			begin
			    select @normal_cost = PSLT_Cost_Normal from Process_Step_Location_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id
		 	    if @ship_qty is not null and @ship_qty <> 0
		  	    begin
				insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Lot_Cost, Lot_Current_Status, Timestamp) values (@process_step_id, @location_id, @lot_number, (@normal_cost * @ship_qty), 'Close', CURRENT_TIMESTAMP)
			    end
			    select @lot_id = @@IDENTITY
			   -- print 'the lotid' + convert(varchar(50),@lot_id)
		        end
		end	
		if @current_location = 'Test' and @ps_type = 'Bake&Pack'  and @vendor_name = 'GTC'
		begin
		   select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @part_number and  PS_Type_Name like 'Test'  and product.ProcessStep_ID=Process_Step.ProcessStep_ID 
		   if @process_step_id is null
		   begin
			-- dummy update to avoid SQLException
			update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
			return
  		   end
	 	   select @location_id = Location_ID from Location where L_Type = 'Test' and L_Vendor_Name = @vendor_name
		   select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
		   
		  if @lot_id is null
	 	   begin
	   		select @process_step_id =ProcessStep_ID from fn_get_process_flow_by_processstep_id(@process_step_id)  where ps_type='FT1'
			 select @location_id = Location_ID from Location where L_Type = 'Test' and L_Vendor_Name = @vendor_name
			if @process_step_id is null -- new Product or Product Name mismatch
			begin
				select @process_step_id = Process_Step.ProcessStep_ID from Product_Map_Table,Process_Step where Product_Map_Table.part_number = @part_number and PS_Type_Name='Test' and PS_Type='Mark' and Product_Map_Table.ProcessStep_ID=Process_Step.ProcessStep_ID and Product_Map_Table.Location_ID = @location_id
				if @process_step_id is null
				begin
					exec sp_create_update_miscellaneous_lot @part_number, @lot_number, null, null, null,
					@vendor_name,'Mark', 'Test', null, @ship_date, null, null, null, null, null, @ship_qty, null, null, null,'TRANSIT'

					-- dummy update to avoid SQLException
					update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
					return
				end
			end

			select  @lot_id = lot_id from lot_table where lot_number = @lot_number  and ProcessStep_ID = @process_step_id
			
			if @lot_id is null
			begin
			    select @normal_cost = PSLT_Cost_Normal from Process_Step_Location_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id
		 	    if @ship_qty is not null and @ship_qty <> 0
		  	    begin
				insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Lot_Cost, Lot_Current_Status, Timestamp) values (@process_step_id, @location_id, @lot_number, (@normal_cost * @ship_qty), 'Close', CURRENT_TIMESTAMP)
			    end
			    select @lot_id = @@IDENTITY
		        end
		  end	
	      end	

  end

